package db;
import java.sql.*;
import java.util.*;
import beans.*;

public class doctorSchedulingDAO extends ConnBean{
	private Connection connection=null;
	public List<doctorSchedulingBean> getAllRecords(){
		ResultSet rs=null;
		Statement stmt=null;
		ArrayList <doctorSchedulingBean> list=new ArrayList<doctorSchedulingBean>();
		try {	
			connection = getConnection();
			if(connection==null)
				System.out.println("error");
			String sql="select * from YY_doctor_Scheduling where state='正常'";
			stmt =connection.createStatement();
			rs=stmt.executeQuery(sql);
			while(rs.next()){
				doctorSchedulingBean doctorScheduling=new doctorSchedulingBean();
				doctorScheduling.setId(rs.getInt(1));
				doctorScheduling.setMzid(rs.getString(2));
				doctorScheduling.setDocid(rs.getString(3));
				doctorScheduling.setDate(rs.getDate(4));
				doctorScheduling.setType(rs.getString(5));
				doctorScheduling.setSum(rs.getInt(6));
				doctorScheduling.setState(rs.getString(7));
				list.add(doctorScheduling);
			}
			stmt.close();						
			connection.close();			
			
	}catch (SQLException e) {
		e.printStackTrace();
	} finally{
		try{
			if (connection!=null && (!connection.isClosed())){
				connection.close();
			}
		}catch(SQLException e){
			e.printStackTrace();
		}
	}
		return list;
	}
	
	public doctorSchedulingBean getRecord(int doctorSchedulingid){
		ResultSet rs=null;
		Statement stmt=null;
		doctorSchedulingBean doctorScheduling=null;
		try {			
			connection = getConnection();
			if(connection==null)
				System.out.println("error");
			String sql="select * from YY_doctor_Scheduling where id='"+doctorSchedulingid+"'";
			stmt =connection.createStatement();
			rs=stmt.executeQuery(sql);
			if(rs!=null && rs.next()){
				doctorScheduling=new doctorSchedulingBean();
				doctorScheduling.setId(rs.getInt(1));
				doctorScheduling.setMzid(rs.getString(2));
				doctorScheduling.setDocid(rs.getString(3));
				doctorScheduling.setDate(rs.getDate(4));
				doctorScheduling.setType(rs.getString(5));
				doctorScheduling.setSum(rs.getInt(6));
				doctorScheduling.setState(rs.getString(7));
			}
			else
			{	
				System.out.println("null");
				doctorScheduling=null;
			}
			stmt.close();					
			connection.close();	
		}catch (SQLException e) {
			e.printStackTrace();
		} finally{
		try{
			if (connection!=null && (!connection.isClosed())){
				connection.close();
			}
		}catch(SQLException e){
			e.printStackTrace();
		}
	}
		return doctorScheduling;
  }	
	
	public boolean insertRecord(doctorSchedulingBean record)
	{
		if(record==null) 
			return false;
		ResultSet rs=null;
		Statement stmt=null;
		boolean result=false;
		try {			
			connection = getConnection();
			if(connection==null)
				System.out.println("error");
			String sql="select * from YY_doctor_Scheduling where mzid='"+record.getMzid()+"' and docid='"+record.getDocid()+"' and type='"+record.getType()+"' and date='"+record.getDate()+"'";
			stmt =connection.createStatement();
			rs=stmt.executeQuery(sql);
			if(rs!=null && rs.next()){
				return false;
			}
			else
			{	
				sql="insert into YY_doctor_Scheduling(mzid,docid,date,type,sum,state) values('"+record.getMzid()+"','"+record.getDocid()+"','"+record.getDate()+"','"+record.getType()+"',"+record.getSum()+",'"+record.getState()+"')";
				stmt.execute(sql);
				result=true;
			}
			stmt.close();					
			connection.close();
		}catch (SQLException e) {
			e.printStackTrace();
		} finally{
			try{
				if (connection!=null && (!connection.isClosed())){
					connection.close();
				}
			}catch(SQLException e){
				e.printStackTrace();
			}
		}
		return result;
	}

	public boolean deleteRecord(int schid)
	{
		PreparedStatement pstmt=null;
		String sql="update YY_doctor_Scheduling set state='取消' where id='"+schid+"'";
		int result=0;
		try{
			connection=getConnection();
			pstmt=connection.prepareStatement(sql);
			result=pstmt.executeUpdate();
			pstmt.close();						
			connection.close();	
		}catch (SQLException e) {
			e.printStackTrace();
		} finally{
			try{
				if (connection!=null && (!connection.isClosed())){
					connection.close();
				}
			}catch(SQLException e){
				e.printStackTrace();
			}
		}
		if(result>0)
			return true;
		else
			return false;
	}
	
	public boolean updateRecord(doctorSchedulingBean beforeRecord,doctorSchedulingBean afterRecord)
	{
		if(beforeRecord==null || afterRecord==null) 
			return false;
		PreparedStatement pstmt=null;
		String sql="update YY_doctor_Scheduling set mzid=?,date=?,type=?,sum=?"+"where id="+beforeRecord.getId();
		int result=0;
		try{
			connection=getConnection();
			pstmt=connection.prepareStatement(sql);
			pstmt.setString(1, afterRecord.getMzid());
			pstmt.setDate(2, afterRecord.getDate());
			pstmt.setString(3, afterRecord.getType());
			pstmt.setInt(4, afterRecord.getSum());
			result=pstmt.executeUpdate();
			pstmt.close();					
			connection.close();	
		}catch (SQLException e) {
			e.printStackTrace();
		} finally{
			try{
				if (connection!=null && (!connection.isClosed())){
					connection.close();
				}
			}catch(SQLException e){
				e.printStackTrace();
			}
		}
		if(result>0)
			return true;
		else
			return false;
	}

	public List<doctorSchedulingBean> getdoctorSchedulingbymz(String mz) {
		ResultSet rs=null;
		Statement stmt=null;
		ArrayList <doctorSchedulingBean> list=new ArrayList<doctorSchedulingBean>();
		try {	
			connection = getConnection();
			if(connection==null)
				System.out.println("error");
			String sql="select * from YY_doctor_Scheduling where mzid='"+mz+"' and state='正常'";
			stmt =connection.createStatement();
			rs=stmt.executeQuery(sql);
			
			while(rs.next()){
				doctorSchedulingBean doctorScheduling=new doctorSchedulingBean();
				doctorScheduling.setId(rs.getInt(1));
				doctorScheduling.setMzid(rs.getString(2));
				doctorScheduling.setDocid(rs.getString(3));
				doctorScheduling.setDate(rs.getDate(4));
				doctorScheduling.setType(rs.getString(5));
				doctorScheduling.setSum(rs.getInt(6));
				doctorScheduling.setState(rs.getString(7));
				list.add(doctorScheduling);
			}
			stmt.close();						
			connection.close();			
			
	}catch (SQLException e) {
		e.printStackTrace();
	} finally{
		try{
			if (connection!=null && (!connection.isClosed())){
				connection.close();
			}
		}catch(SQLException e){
			e.printStackTrace();
		}
	}
		return list;
	}

	public List<doctorSchedulingBean> getdoctorSchedulingbyks(String ks) {
		ResultSet rs=null;
		Statement stmt=null;
		ArrayList <doctorSchedulingBean> list=new ArrayList<doctorSchedulingBean>();
		try {	
			connection = getConnection();
			if(connection==null)
				System.out.println("error");
			String sql="select * from YY_doctor_Scheduling where mzid in(select id from YY_mz where ksid='"+ks+"') and state='正常'";
			stmt =connection.createStatement();
			rs=stmt.executeQuery(sql);
			while(rs.next()){
				doctorSchedulingBean doctorScheduling=new doctorSchedulingBean();
				doctorScheduling.setId(rs.getInt(1));
				doctorScheduling.setMzid(rs.getString(2));
				doctorScheduling.setDocid(rs.getString(3));
				doctorScheduling.setDate(rs.getDate(4));
				doctorScheduling.setType(rs.getString(5));
				doctorScheduling.setSum(rs.getInt(6));
				doctorScheduling.setState(rs.getString(7));
				list.add(doctorScheduling);
			}
			stmt.close();						
			connection.close();			
			
	}catch (SQLException e) {
		e.printStackTrace();
	} finally{
		try{
			if (connection!=null && (!connection.isClosed())){
				connection.close();
			}
		}catch(SQLException e){
			e.printStackTrace();
		}
	}
		return list;
	}

	public List<doctorSchedulingBean> getdoctorSchedulingbydate(String date) {
		ResultSet rs=null;
		Statement stmt=null;
		ArrayList <doctorSchedulingBean> list=new ArrayList<doctorSchedulingBean>();
		try {	
			connection = getConnection();
			if(connection==null)
				System.out.println("error");
			String sql="select * from YY_doctor_Scheduling where state='正常' and date='"+date+"'";
			stmt =connection.createStatement();
			rs=stmt.executeQuery(sql);
			while(rs.next()){
				doctorSchedulingBean doctorScheduling=new doctorSchedulingBean();
				doctorScheduling.setId(rs.getInt(1));
				doctorScheduling.setMzid(rs.getString(2));
				doctorScheduling.setDocid(rs.getString(3));
				doctorScheduling.setDate(rs.getDate(4));
				doctorScheduling.setType(rs.getString(5));
				doctorScheduling.setSum(rs.getInt(6));
				doctorScheduling.setState(rs.getString(7));
				list.add(doctorScheduling);
			}
			stmt.close();						
			connection.close();			
			
	}catch (SQLException e) {
		e.printStackTrace();
	} finally{
		try{
			if (connection!=null && (!connection.isClosed())){
				connection.close();
			}
		}catch(SQLException e){
			e.printStackTrace();
		}
	}
		return list;
	}

	public List<doctorSchedulingBean> getdoctorSchedulingbyksdate(String id, String date) {
		ResultSet rs=null;
		Statement stmt=null;
		ArrayList <doctorSchedulingBean> list=new ArrayList<doctorSchedulingBean>();
		try {	
			connection = getConnection();
			if(connection==null)
				System.out.println("error");
			String sql="select * from YY_doctor_Scheduling where mzid in(select id from YY_mz where ksid='"+id+"') and state='正常' and date='"+date+"'";
			stmt =connection.createStatement();
			rs=stmt.executeQuery(sql);
			while(rs.next()){
				doctorSchedulingBean doctorScheduling=new doctorSchedulingBean();
				doctorScheduling.setId(rs.getInt(1));
				doctorScheduling.setMzid(rs.getString(2));
				doctorScheduling.setDocid(rs.getString(3));
				doctorScheduling.setDate(rs.getDate(4));
				doctorScheduling.setType(rs.getString(5));
				doctorScheduling.setSum(rs.getInt(6));
				doctorScheduling.setState(rs.getString(7));
				list.add(doctorScheduling);
			}
			stmt.close();						
			connection.close();			
			
	}catch (SQLException e) {
		e.printStackTrace();
	} finally{
		try{
			if (connection!=null && (!connection.isClosed())){
				connection.close();
			}
		}catch(SQLException e){
			e.printStackTrace();
		}
	}
		return list;
	}

	public List<doctorSchedulingBean> getdoctorSchedulingbymzdate(String mz, String date) {
		ResultSet rs=null;
		Statement stmt=null;
		ArrayList <doctorSchedulingBean> list=new ArrayList<doctorSchedulingBean>();
		try {	
			connection = getConnection();
			if(connection==null)
				System.out.println("error");
			String sql="select * from YY_doctor_Scheduling where mzid='"+mz+"' and state='正常' and date='"+date+"'";
			stmt =connection.createStatement();
			rs=stmt.executeQuery(sql);
			
			while(rs.next()){
				doctorSchedulingBean doctorScheduling=new doctorSchedulingBean();
				doctorScheduling.setId(rs.getInt(1));
				doctorScheduling.setMzid(rs.getString(2));
				doctorScheduling.setDocid(rs.getString(3));
				doctorScheduling.setDate(rs.getDate(4));
				doctorScheduling.setType(rs.getString(5));
				doctorScheduling.setSum(rs.getInt(6));
				doctorScheduling.setState(rs.getString(7));
				list.add(doctorScheduling);
			}
			stmt.close();						
			connection.close();			
			
	}catch (SQLException e) {
		e.printStackTrace();
	} finally{
		try{
			if (connection!=null && (!connection.isClosed())){
				connection.close();
			}
		}catch(SQLException e){
			e.printStackTrace();
		}
	}
		return list;
	}
	
	public List<doctorSchedulingBean> getdoctorSchedulingbydoctor(String docid) {
		ResultSet rs=null;
		Statement stmt=null;
		ArrayList <doctorSchedulingBean> list=new ArrayList<doctorSchedulingBean>();
		try {	
			connection = getConnection();
			if(connection==null)
				System.out.println("error");
			String sql="select * from YY_doctor_Scheduling where docid='"+docid+"' and state='正常'";
			stmt =connection.createStatement();
			rs=stmt.executeQuery(sql);
			
			while(rs.next()){
				doctorSchedulingBean doctorScheduling=new doctorSchedulingBean();
				doctorScheduling.setId(rs.getInt(1));
				doctorScheduling.setMzid(rs.getString(2));
				doctorScheduling.setDocid(rs.getString(3));
				doctorScheduling.setDate(rs.getDate(4));
				doctorScheduling.setType(rs.getString(5));
				doctorScheduling.setSum(rs.getInt(6));
				doctorScheduling.setState(rs.getString(7));
				list.add(doctorScheduling);
			}
			stmt.close();						
			connection.close();			
			
	}catch (SQLException e) {
		e.printStackTrace();
	} finally{
		try{
			if (connection!=null && (!connection.isClosed())){
				connection.close();
			}
		}catch(SQLException e){
			e.printStackTrace();
		}
	}
		return list;
	}
	
}
